﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Dynamic;
using System.Data.SqlClient;
using System.Configuration;

    public class CommonDAL
    {
        //public static string conn = "Data Source=42.236.85.97;Initial Catalog=mall;User ID=sa;Password=Gaobo1987";
        //public static string conn = "Data Source=101.201.114.160;Initial Catalog=master;User ID=sa;Password=weichuantianxia";
        public static string conn = "Data Source=;Initial Catalog=master;User ID=;Password=";
        //增删改执行语句
        public static bool ExecuteNonQuery(string proc, SqlParameter[] para)
        {
            int result = SQLHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, proc, para);
            return result == 0 ? false : true;
        }
        public static bool ExecuteNoneQuery(string text, SqlParameter[] para)
        {
            int result = SQLHelper.ExecuteNonQuery(conn, CommandType.Text, text, para);
            return result == 0 ? false : true;
        }
        public static bool ExecuteNoneQuery(string text)
        {
            int result = SQLHelper.ExecuteNonQuery(conn, CommandType.Text, text, null);
            return result == 0 ? false : true;
        }
        public static SqlDataReader ExecuteReader(string proc, SqlParameter[] para)
        {
            return SQLHelper.ExecuteReader(conn, CommandType.StoredProcedure, proc, para);
        }
        public static object ExecuteScalar(string proc, SqlParameter[] para)
        {
            return SQLHelper.ExecuteScalar(conn, CommandType.StoredProcedure, proc, para);
        }
        public static object ExecuteScalar(string text)
        {
            return SQLHelper.ExecuteScalar(conn, CommandType.Text, text, null);
        }
        public static DataTable GetDataTable(string proc, SqlParameter[] para)
        {
            return SQLHelper.GetDataTable(conn, CommandType.StoredProcedure, proc, para);
        }
        public static DataTable GetDataTable(string text)
        {
            return SQLHelper.GetDataTable(conn, CommandType.Text, text, null);
        }
        public static DataRow GetDataRow(string proc, SqlParameter[] para)
        {
            return SQLHelper.GetDataRow(conn, CommandType.StoredProcedure, proc, para);
        }
        public static DataRow GetDataRow(string text)
        {
            return SQLHelper.GetDataRow(conn, CommandType.Text, text, null);
        }
        public static bool ExecuteTransaction(string flag,string[] proc, SqlParameter[][] para)
        {
            return SQLHelper.ExecuteTransaction(flag, conn, CommandType.StoredProcedure, proc, para);
        }

        public static List<string> FindDataBase()
        {
            DataTable dt = GetDataTable("select name,status2 from master..sysdatabases");
            List<string> list = new List<string>();
            foreach (DataRow row in dt.Rows)
            {
                if (row["status2"].ToString() == "1627389952")
                {
                    list.Add(row["name"].ToString());
                }
            }
            return list;
        }
        public static List<dynamic> FindDataTable()
        {
            DataTable dt = GetDataTable("select   a.name AS tablename,isnull(g.[value],'') AS tablebrief from sys.tables a left join sys.extended_properties g on (a.object_id = g.major_id AND g.minor_id = 0)");
            List<dynamic> list = new List<dynamic>();
            foreach (DataRow row in dt.Rows)
            {
                dynamic obj = new ExpandoObject();
                obj.tablename = DataConvert.ToString(row["tablename"]);
                obj.tablebrief = DataConvert.ToString(row["tablebrief"]);
                list.Add(obj);
            }
            return list;
        }
        public static List<dynamic> FindDataColumn(string table)
        {
            DataTable dt = GetDataTable("select a.name,a.max_length as length,CAST(g.value AS nvarchar) as brief,t.name as type from sys.columns a left join sys.extended_properties g on (a.object_id = g.major_id AND a.column_id=g.minor_id) left join  sys.types t on a.system_type_id=t.system_type_id where object_id=OBJECT_ID('"+table+"') order by object_id,a.column_id");
            List<dynamic> list = new List<dynamic>();
            foreach (DataRow row in dt.Rows)
            {
                dynamic dy = new ExpandoObject();
                dy.name = DataConvert.ToString(row["name"]);
                dy.type = DataConvert.ToString(row["type"]);
                dy.length = DataConvert.ToString(row["length"]);
                dy.brief = DataConvert.ToString(row["brief"]);
                list.Add(dy);
            }
            return list;
        }
        
        
    }

